package com.system.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

import com.system.dao.ScoreDao;
import com.system.model.CourseScorePojo;
import com.system.model.Score;
import com.system.model.ScorePojo;
import com.system.util.JdbcUtil;
import com.system.util.ResultMapUtil;

public class ScoreDaoImpl extends BaseDaoImpl implements ScoreDao {

	public ScoreDaoImpl() throws Exception {
		super();
		// TODO Auto-generated constructor stub
	}
	private List<ScorePojo> find(String sql){
		try{
			ResultSet rs=this.templateQuery(sql);
			List<ScorePojo> scores=new ArrayList();
			while(rs.next()) {
				ScorePojo score=new ScorePojo();
				Map<String,String> map=ResultMapUtil.getResultMap(rs);
				try{
					BeanUtils.populate(score, map);
					scores.add(score);
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			return scores;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	private List<CourseScorePojo> findTeaScore(String sql){
		try{
			ResultSet rs=this.templateQuery(sql);
			List<CourseScorePojo> scores=new ArrayList();
			while(rs.next()) {
				CourseScorePojo score=new CourseScorePojo();
				Map<String,String> map=ResultMapUtil.getResultMap(rs);
				try{
					BeanUtils.populate(score, map);
					scores.add(score);
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			return scores;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<ScorePojo> listScoresbyClassMajor(String clazz, String major) {
		String sql="select student.stunum,student.stuname,student.stuclass,score.score,course.coursename from student,score,course where score.stu_id=student.id and score.course_id=course.id "
				+ "and student.stuclass="+"'"+clazz+"'"+ " and student.major="+"'"+major+"'";
		List<ScorePojo> scores=find(sql);
		JdbcUtil.getInstance().free(conn);
		return scores;
	}
	@Override
	public boolean add(Score score) {
		String sql="insert into "+"score(tea_id,stu_id"+
				",course_id,score)"+ "values"+"("+
				+score.getTea_id()+
				","+score.getStu_id()+
				","+score.getCourse_id()+
				
				","+score.getScore()+")";
		try{
			this.templateUpdate(sql);
			return true;
		}
		catch(Exception e){
			e.printStackTrace();
			return false;
		}
		finally{
			JdbcUtil.getInstance().free(conn);
		}
	}
	@Override
	public List<CourseScorePojo> listScoresbyteaid(Integer tea_id) {
		String sql="select student.stuname,student.stuclass,course.coursename,score.score from student,course,score,teacher"+" where student.id=score.stu_id and course.id=score.course_id and teacher.id=score.tea_id and score.tea_id="+tea_id;
		List<CourseScorePojo> list=this.findTeaScore(sql);
		JdbcUtil.getInstance().free(conn);
		return list;
	}
	@Override
	public List<String> listStunums(String clazz, String major) {
		String sql="select distinct student.stunum from score,student,course,teacher where score.stu_id=student.id and score.tea_id=teacher.id and score.course_id=course.id and student.major="+"'"+major+"'"+ "and student.stuclass='"+clazz+"'";
		try {
			List<String> nums=new ArrayList<>();
			ResultSet rs=this.templateQuery(sql);
			while(rs.next()){
				nums.add(rs.getString(1));
			}
			return nums;
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}

}
